const config = require("./../../config")
const mysql = require("mysql")
const querystring = require('querystring')
const schedule = require('node-schedule');


const pool1 = mysql.createPool({
  host     :  config.database1.HOST,
  user     : config.database1.USERNAME,
  password : config.database1.PASSWORD,
  database : config.database1.DATABASE
})

const pool2 = mysql.createPool({
  host     :  config.database2.HOST,
  user     : config.database2.USERNAME,
  password : config.database2.PASSWORD,
  database : config.database2.DATABASE
})


let query = function( sql, values ) {

  return new Promise(( resolve, reject ) => {
    pool1.getConnection(function(err, connection) {
      if (err) {
        resolve( err )
      } else {
        connection.query(sql, values, ( err, rows) => {

          if ( err ) {
            reject( err )
          } else {
            resolve( rows )
            // console.log(rows)
          }
          connection.release()
        })
      }
    })
  })

}

let query1 = function( sql, values ) {

  return new Promise(( resolve, reject ) => {
    pool2.getConnection(function(err, connection) {
      if (err) {
        resolve( err )
      } else {
        connection.query(sql, values, ( err, rows) => {

          if ( err ) {
            reject( err )
          } else {
            resolve( rows )
            // console.log(rows)
          }
          connection.release()
        })
      }
    })
  })
}

let WeekAgotime = Math.round(new Date().getTime()/1000) - 24 * 60 * 60  //设置备份为三个月内的数据
let _WeekAgotime = WeekAgotime


//公用方法
const transform={
  //备份时间点
  async excuteDate(table,keys,time){
    let _sql = `SELECT * FROM ?? WHERE tbplaylog.time > ? LIMIT 1`
    return query(_sql, keys, table, time)
},
  
  //第一条记录
  async getFirstRec(table, keys){
    let _sql = `select ?? from ?? limit 1`
    return query(_sql, keys, table)
},
  // const parsequery = function(json, Key){
  //   let queryArr = [];
  // 　for(var i=0;i<json.length;i++){
  // 　　　for(var key in json[i]){
  //         if (key == Key) {
  //           queryArr.push(json[i][key])
  //         }
  //     }
  // 　}
  // console.log(queryArr.toString())
  // return  queryArr.toString()
  // }
  
  // let Pid = parsequery(json, 'Pid')
  // let devno = parsequery(json, 'devno')
  // let content = parsequery(json, 'content')
  // let type = parsequery(json, 'type')
  // let time = parsequery(json, 'time')
  // let note = parsequery(json, 'note')

  // let querystring = Pid + devno + content + type + time + note
  // console.log(querystring)


}




// (function scheduleCronstyle(){
//     schedule.scheduleJob('0 0 3 3 * * *',function(){
//         console.log('scheduleCronstyle:'+new Date())
//         transform.count()  //每天3:30执行
// })
// })()



const tbplaylog = {
  async excuteDate(){
    let _sql = `SELECT StartTime FROM tbplaylog WHERE tbplaylog.StartTime > ? LIMIT 1`
    return query(_sql, _WeekAgotime)
  },
  async getFirstRec(){
    let _sql = `select * from tbplaylog limit 1`
    return query(_sql)
  },
    //查旧表 
  async queryOldTable(){
    let Pid = await tbplaylog.getFirstRec('Pid')
    let StartTime = await tbplaylog.excuteDate('StartTime','tbplaylog',_WeekAgotime)
    Pid = Pid[0].Pid
    StartTime = StartTime[0].StartTime
    let _sql = `SELECT * FROM tbplaylog WHERE tbplaylog.StartTime < ? ORDER BY Pid LIMIT 1`
    return query(_sql, StartTime)
  },

  async insertData() {
    let data = await tbplaylog.queryOldTable()
    let string=JSON.stringify(data)
    let json =  JSON.parse(string)
    //拼接查询

    var parsequery = function(json){
      let queryArr = ""
    　for(var i=0;i<json.length;i++){
          var one = ""
    　　　for(var key in json[i]){
            var str = "'" + json[i][key] + "'"
            one = one == "" ? "(" + json[i][key] : one  + ","  + str  ;
        }
        one = one + ")"
        queryArr = queryArr == "" ? one : queryArr + "," + one
      }
      console.log(queryArr)
      return queryArr
    }
    json = parsequery(json)
   // let transArr = parsequery(json)

   //  function chunk(arr, size) {
   //    var arr2=[];
   //    for(var i=0;i<arr.length;i=i+size){
   //      arr2.push(arr.slice(i,i+size));
   //    }
   //    return arr2;
   //  }
   //  let querystring = chunk(transArr,6)
   //  console.log(querystring)

    let _sql = "INSERT INTO `tbplaylog_copy`(`Pid`,`devno`,`content`,`type`,`StartTime`,`note`) VALUES " + json
    // let _sql = "INSERT INTO tbplaylog_copy SET ?"
    // return query( _sql,(Pid,devno,content,type,time,note))
    return query1( _sql)
  },


  async deleteData () {
    let StartTime = await tbplaylog.excuteDate()
    StartTime = StartTime[0].StartTime
    let deleteData = await tbplaylog.insertData()
    console.log('success')
    let _sql = `DELETE FROM tbplaylog WHERE tbplaylog.StartTime < ? ORDER BY Pid LIMIT 1`
    return query( _sql, StartTime )
},

  async count() {
    await tbplaylog.deleteData()
    let startTime = new Date().getTime()/1000
    let firstDate = await tbplaylog.getFirstRec('StartTime')
    let StartTime = await tbplaylog.excuteDate(_WeekAgotime)
    StartTime = StartTime[0].StartTime
    firstDate = firstDate[0].StartTime
    firstDate < StartTime ? tbplaylog.count() : (()=>{
      console.log('tbDeviceRec备份完成');
      console.log(new Date().getTime()/1000 - startTime )
    })()
  }
}

tbplaylog.count()



